package vn.edu.ptithcm.pim.dao;

import java.util.ArrayList;
import java.util.List;

import org.apache.commons.lang.StringUtils;
import org.hibernate.Criteria;
import org.hibernate.LockMode;
import org.hibernate.Transaction;
import org.hibernate.criterion.Criterion;
import org.hibernate.criterion.Disjunction;
import org.hibernate.criterion.Order;
import org.hibernate.criterion.ProjectionList;
import org.hibernate.criterion.Projections;
import org.hibernate.criterion.Restrictions;
import org.springframework.orm.hibernate3.support.HibernateDaoSupport;

import vn.edu.ptithcm.pim.dom.Employee;
import vn.edu.ptithcm.pim.dom.EmployeeFunctionInProject;
import vn.edu.ptithcm.pim.dom.Function;
import vn.edu.ptithcm.pim.query.EmployeeQuery;

@SuppressWarnings({ "unchecked", "rawtypes" })
public class EmployeeDao extends HibernateDaoSupport {

	public void save(Employee employee) {
		try {
			Transaction tx = getSession().beginTransaction();
			getHibernateTemplate().save(employee);
			tx.commit();
		} catch (RuntimeException re) {
			throw re;
		}
	}

	public void saveOrUpdate(Employee employee) {
		try {
			Transaction tx = getSession().beginTransaction();
			if (employee.getEmployeeid() != null) {
				getHibernateTemplate().merge(employee);
			} else {
				getHibernateTemplate().saveOrUpdate(employee);
			}
			tx.commit();
		} catch (RuntimeException re) {
			throw re;
		}
	}

	public void lock(Employee employee) {
		try {
			Transaction tx = getSession().beginTransaction();
			getHibernateTemplate().lock(employee, LockMode.NONE);
			tx.commit();
		} catch (RuntimeException re) {
			throw re;
		}
	}

	public void delete(Employee employee) {
		try {
			Transaction tx = getSession().beginTransaction();
			getHibernateTemplate().delete(employee);
			tx.commit();
		} catch (RuntimeException re) {
			throw re;
		}
	}

	public Employee merge(Employee employee) {
		try {
			return (Employee) getHibernateTemplate().merge(employee);
		} catch (RuntimeException re) {
			throw re;
		}
	}

	public Employee findById(java.lang.Long id) {
		try {
			return (Employee) getHibernateTemplate().get(Employee.class, id);
		} catch (RuntimeException re) {
			throw re;
		}
	}

	public List getEmployeesInProjectBySearchKey(Long projectId, Long rankId,
			Long diplomaId, String language, String searchKey) {
		try {
			List employees = new ArrayList(0);
			if ("Keyword...".equals(searchKey)) {
				searchKey = "";
			}
			if ("".equals(searchKey) && rankId.intValue() == 0
					&& diplomaId.intValue() == 0) {
				employees = getMembersInProject(projectId);
			} else {
				Criteria c = getSession().createCriteria(
						EmployeeFunctionInProject.class);

				ProjectionList proList = Projections.projectionList();
				proList.add(Projections.property("employee"));
				c.setProjection(proList);
				c.createAlias("project", "project");
				c.add(Restrictions.eq("project.projectid", projectId));
				c.createAlias("employee", "employee");

				if (rankId.intValue() != 0) {
					c.createAlias("employee.rank", "rank");
					c.add(Restrictions.eq("rank.rankid", rankId));
				}
				if (diplomaId.intValue() != 0) {
					c.createAlias("employee.diploma", "diploma");
					c.add(Restrictions.eq("diploma.diplomaid", diplomaId));
				}
				if (!"".equals(language)) {
					c.add(Restrictions.ilike("employee.languages", "%"
							+ language + "%"));
				}
				if (!"".equals(searchKey)) {
					Criterion visa = Restrictions.ilike("employee.visa", "%"
							+ searchKey + "%");
					Criterion firstname = Restrictions.ilike(
							"employee.firstname", "%" + searchKey + "%");
					Criterion lastname = Restrictions.ilike(
							"employee.lastname", "%" + searchKey + "%");
					Disjunction disjunction = Restrictions.disjunction();
					disjunction.add(visa);
					disjunction.add(firstname);
					disjunction.add(lastname);
					c.add(disjunction);
				}
				c.addOrder(Order.asc("employee.firstname"));
				employees.addAll(c.list());
			}
			return employees;
		} catch (RuntimeException re) {
			throw re;
		}
	}

	public List findByExample(Employee employee) {
		try {
			return getHibernateTemplate().findByExample("Employee", employee);
		} catch (RuntimeException re) {
			throw re;
		}
	}

	public List getAll() {
		try {
			return getHibernateTemplate().loadAll(Employee.class);
		} catch (RuntimeException re) {
			throw re;
		}
	}

	public List getMembersForProjectBySearchKey(Long projectId, Long rankId,
			Long diplomaId, String language, String searchKey) {
		try {
			List employees = new ArrayList(0);
			if ("Keyword...".equals(searchKey)) {
				searchKey = "";
			}
			if (searchKey.isEmpty() && language.isEmpty()
					&& rankId.intValue() == 0 && diplomaId.intValue() == 0) {
				employees = getMembersForProject(projectId);
			} else {
				Criteria c1 = getSession().createCriteria(Employee.class);
				c1.createAlias("department", "department");
				c1.add(Restrictions.eq("department.departmentid", new Long(4)));

				Criteria c2 = getSession().createCriteria(
						EmployeeFunctionInProject.class);
				c2.createAlias("employee", "employee");
				c2.createAlias("project", "project");
				c2.add(Restrictions.eq("project.projectid", projectId));
				ProjectionList proList = Projections.projectionList();
				proList.add(Projections.property("employee.employeeid"));
				c2.setProjection(proList);

				if (c2.list().size() > 0) {
					c1.add(Restrictions.not(Restrictions.in("employeeid",
							c2.list())));
				}

				if (rankId.intValue() != 0) {
					c1.createAlias("rank", "rank");
					c1.add(Restrictions.eq("rank.rankid", rankId));
				}
				if (diplomaId.intValue() != 0) {
					c1.createAlias("diploma", "diploma");
					c1.add(Restrictions.eq("diploma.diplomaid", diplomaId));
				}
				if (!language.isEmpty()) {
					c1.add(Restrictions
							.ilike("languages", "%" + language + "%"));
				}
				if (!searchKey.isEmpty()) {
					Criterion visa = Restrictions.ilike("visa", "%" + searchKey
							+ "%");
					Criterion firstname = Restrictions.ilike("firstname", "%"
							+ searchKey + "%");
					Criterion lastname = Restrictions.ilike("lastname", "%"
							+ searchKey + "%");
					Disjunction disjunction = Restrictions.disjunction();
					disjunction.add(visa);
					disjunction.add(firstname);
					disjunction.add(lastname);
					c1.add(disjunction);
				}
				c1.addOrder(Order.asc("firstname"));
				employees.addAll(c1.list());
			}
			return employees;
		} catch (RuntimeException re) {
			throw re;
		}
	}

	public List getMembersForProject(Long projectId) {
		try {
			Criteria c1 = getSession().createCriteria(Employee.class);
			c1.createAlias("department", "department");
			c1.add(Restrictions.eq("department.departmentid", new Long(4)));

			Criteria c2 = getSession().createCriteria(
					EmployeeFunctionInProject.class);
			c2.createAlias("employee", "employee");
			c2.createAlias("project", "project");
			c2.add(Restrictions.eq("project.projectid", projectId));

			ProjectionList proList = Projections.projectionList();
			proList.add(Projections.property("employee.employeeid"));
			c2.setProjection(proList);
			if (c2.list().size() > 0) {
				c1.add(Restrictions.not(Restrictions.in("employeeid", c2.list())));
			}
			c1.addOrder(Order.asc("firstname"));
			return c1.list();
		} catch (RuntimeException re) {
			throw re;
		}
	}

	public Function getFunctionInProject(Long projectId, Long employeeId) {
		try {
			StringBuilder sb = new StringBuilder();
			sb.append("SELECT efip.function FROM EmployeeFunctionInProject as efip  WHERE efip.project.projectid='");
			sb.append(projectId).append("' AND efip.employee.employeeid='")
					.append(employeeId).append("'");
			List result = getHibernateTemplate().find(sb.toString());
			return result.size() == 1 ? (Function) result.get(0) : null;
		} catch (RuntimeException re) {
			throw re;
		}
	}

	public Employee findByVisa(String visa) {
		try {
			StringBuilder sb = new StringBuilder();
			sb.append("SELECT employee FROM Employee as employee  WHERE employee.visa='");
			sb.append(visa).append("'");
			List result = getHibernateTemplate().find(sb.toString());
			return result.size() == 1 ? (Employee) result.get(0) : null;
		} catch (RuntimeException re) {
			throw re;
		}
	}

	public List getMembersInProject(Long projectId) {
		try {
			Criteria c = getSession().createCriteria(
					EmployeeFunctionInProject.class);

			ProjectionList proList = Projections.projectionList();
			proList.add(Projections.property("employee"));
			c.setProjection(proList);

			c.createAlias("project", "project");
			c.add(Restrictions.eq("project.projectid", projectId));
			c.createAlias("employee", "employee");
			c.addOrder(Order.asc("employee.firstname"));

			return c.list();
		} catch (RuntimeException re) {
			throw re;
		}
	}

	public void deleteById(java.lang.Long id) {
		try {
			Transaction tx = getSession().beginTransaction();
			StringBuilder sql = new StringBuilder();
			sql.append("delete Employee as emp where emp.employeeid ='")
					.append(id).append("'");
			getHibernateTemplate().bulkUpdate(sql.toString());
			tx.commit();
		} catch (RuntimeException re) {
			throw re;
		}
	}

	public List<Employee> findByQuery(EmployeeQuery query, int max, int offset) {
		try {
			String searchKey = StringUtils.trimToEmpty(query.getSearchKey());
			if ("Keyword...".equals(searchKey)) {
				searchKey = "";
			}
			Criteria c = getSession().createCriteria(Employee.class);
			if (!searchKey.isEmpty()) {
				c.add(Restrictions.ilike("visa", "%" + searchKey + "%"));
			}
			c.addOrder(Order.asc("firstname"));
			return c.setFirstResult(offset).setMaxResults(max).list();
		} catch (RuntimeException re) {
			throw re;
		}
	}

	public int countByQuery(EmployeeQuery query) {
		try {
			String searchKey = StringUtils.trimToEmpty(query.getSearchKey());
			if ("Keyword...".equals(searchKey)) {
				searchKey = "";
			}
			Criteria c = getSession().createCriteria(Employee.class);
			if (!searchKey.isEmpty()) {
				c.add(Restrictions.ilike("visa", "%" + searchKey + "%"));
			}
			c.setProjection(Projections.rowCount());
			return Integer.parseInt(c.list().get(0).toString());
		} catch (RuntimeException re) {
			throw re;
		}
	}
}
